1 Imports System.Data.SqlClient
2 Imports Excel = Microsoft.Office.Interop.Excel
3 Public Class frmAttendanceEntryRecord
4
5 Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
6 Me.Close()
7 End Sub
8
9 Public Sub GetData()
10 Try
11 con = New SqlConnection(cs)
12 con.Open()
13 cmd = New SqlCommand("select RTRIM(StaffAttendance.ID) as [Attendance ID],RTRIM(Staff.St_ID) as [SID],RTRIM(Staff.StaffID) as [Staff ID],RTRIM(StaffName) as [Staff Name], Convert(DateTime,WorkingDate,103) as [Working Date],RTRIM(StaffAttendance.Status) as [Status], RTRIM(InTime) as [In Time],RTRIM(OutTime) as [Out Time] from StaffAttendance,Staff where Staff.St_ID=StaffAttendance.StaffID order by workingdate", con)
14 Dim myDA As SqlDataAdapter = New SqlDataAdapter(cmd)
15 Dim myDataSet As DataSet = New DataSet()
16 myDA.Fill(myDataSet, "StaffAttendance")
17 dgw.DataSource = myDataSet.Tables("StaffAttendance").DefaultView
18 con.Close()
19 Catch ex As Exception
20 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
21 End Try
22 End Sub
23 Private Sub frmLogs_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
24 GetData()
25 End Sub
26 Sub Reset()
27 txtStaffName.Text = ""
28 DateFrom.Text = Today
29 DateTo.Text = Now
30 GetData()
31 End Sub
32 Private Sub btnReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReset.Click
33 Reset()
34 End Sub
35
36
37 Private Sub btnClose_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
38 Me.Close()
39 End Sub
40
41 Private Sub btnExportExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportExcel.Click
42 Dim rowsTotal, colsTotal As Short
43 Dim I, j, iC As Short
44 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
45 Dim xlApp As New Excel.Application
46 Try
47 Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
48 Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
49 xlApp.Visible = True
50
51 rowsTotal = dgw.RowCount
52 colsTotal = dgw.Columns.Count - 1
53 With excelWorksheet
54 .Cells.Select()
55 .Cells.Delete()
56 For iC = 0 To colsTotal
57 .Cells(1, iC + 1).Value = dgw.Columns(iC).HeaderText
58 Next
59 For I = 0 To rowsTotal - 1
60 For j = 0 To colsTotal
61 .Cells(I + 2, j + 1).value = dgw.Rows(I).Cells(j).Value
62 Next j
63 Next I
64 .Rows("1:1").Font.FontStyle = "Bold"
65 .Rows("1:1").Font.Size = 12
66
67 .Cells.Columns.AutoFit()
68 .Cells.Select()
69 .Cells.EntireColumn.AutoFit()
70 .Cells(1, 1).Select()
71 End With
72 Catch ex As Exception
73 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
74 Finally
75 'RELEASE ALLOACTED RESOURCES
76 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
77 xlApp = Nothing
78 End Try
79 End Sub
80
81 Private Sub dgw_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
82 Try
83 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
84 If lblSet.Text = "Attendance Entry" Then
85 Me.Hide()
86 frmAttendance.Show()
87 ' or simply use column name instead of index
88 'dr.Cells["id"].Value.ToString();
89 frmAttendance.txtID.Text = dr.Cells(0).Value.ToString()
90 frmAttendance.txtStID.Text = dr.Cells(1).Value.ToString()
91 frmAttendance.StaffID.Text = dr.Cells(2).Value.ToString()
92 frmAttendance.StaffName.Text = dr.Cells(3).Value.ToString()
93 frmAttendance.WorkingDate.Text = dr.Cells(4).Value.ToString()
94 frmAttendance.Status.Text = dr.Cells(5).Value.ToString()
95 frmAttendance.InTime.Text = dr.Cells(6).Value.ToString()
96 frmAttendance.OutTime.Text = dr.Cells(7).Value.ToString()
97 frmAttendance.btnSave.Enabled = False
98 frmAttendance.btnUpdate.Enabled = True
99 frmAttendance.btnDelete.Enabled = True
100 frmAttendance.WorkingDate.Enabled = False
101 End If
102 Catch ex As Exception
103 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
104 End Try
105
106 End Sub
107
108 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
109 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
110 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
111 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
112 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
113 End If
114 Dim b As Brush = SystemBrushes.ControlText
115 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
116
117 End Sub
118
119 Private Sub txtStaffName_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtStaffName.TextChanged
120 Try
121 con = New SqlConnection(cs)
122 con.Open()
123 cmd = New SqlCommand("select RTRIM(StaffAttendance.ID) as [Attendance ID],RTRIM(Staff.St_ID) as [SID],RTRIM(Staff.StaffID) as [Staff ID],RTRIM(StaffName) as [Staff Name], Convert(DateTime,WorkingDate,103) as [Working Date],RTRIM(StaffAttendance.Status) as [Status], RTRIM(InTime) as [In Time],RTRIM(OutTime) as [Out Time] from StaffAttendance,Staff where Staff.St_ID=StaffAttendance.StaffID and StaffName like '" & txtStaffName.Text & "%' order by workingdate", con)
124 Dim myDA As SqlDataAdapter = New SqlDataAdapter(cmd)
125 Dim myDataSet As DataSet = New DataSet()
126 myDA.Fill(myDataSet, "StaffAttendance")
127 dgw.DataSource = myDataSet.Tables("StaffAttendance").DefaultView
128 con.Close()
129 Catch ex As Exception
130 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
131 End Try
132 End Sub
133
134 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
135 Try
136 con = New SqlConnection(cs)
137 con.Open()
138 cmd = New SqlCommand("select RTRIM(StaffAttendance.ID) as [Attendance ID],RTRIM(Staff.St_ID) as [SID],RTRIM(Staff.StaffID) as [Staff ID],RTRIM(StaffName) as [Staff Name], Convert(DateTime,WorkingDate,103) as [Working Date],RTRIM(StaffAttendance.Status) as [Status], RTRIM(InTime) as [In Time],RTRIM(OutTime) as [Out Time] from StaffAttendance,Staff where Staff.St_ID=StaffAttendance.StaffID and WorkingDate Between @d1 and @d2 order by workingdate", con)
139 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "DateIN").Value = DateFrom.Value.Date
140 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "DateIN").Value = DateTo.Value
141 Dim myDA As SqlDataAdapter = New SqlDataAdapter(cmd)
142 Dim myDataSet As DataSet = New DataSet()
143 myDA.Fill(myDataSet, "StaffAttendance")
144 dgw.DataSource = myDataSet.Tables("StaffAttendance").DefaultView
145 con.Close()
146 Catch ex As Exception
147 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
148 End Try
149 End Sub
150 End Class
